/** 
 * @Title: SlrankDao.java 
 * @Package com.zizo.autohome.sta.slrank 
 * @Description: TODO
 * @author HeYan
 * @date 2016年10月22日 下午3:12:08 
 */
package com.zizo.autohome.sta.slrank.dao;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;
import java.util.Map;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.Executors;

import com.zizo.autohome.sta.slrank.util.Slrank;
import com.zizo.autohome.sta.slrank.util.SlrankUtil;
import com.zizo.autohome.sta.slrank.util.TableUtil;
import com.zizo.util.ConnectionPool;

/**
 * @ClassName: SlrankDao
 * @Description: TODO 计算BBS&KB的声量
 * @author HeYan
 * @date 2016年10月22日 下午3:12:08
 * 
 */
public class Slrank1Dao implements Runnable {
	private Connection conn;
	private String tablename;
	private Map<Integer, String> carname;
	private Map<String, String> city;

	/**
	 * @Title: calculateBBS
	 * @Description: TODO 计算声量
	 * @return void
	 * @author Alexis
	 */
	public void calculateBBS() {
		Statement st = null;
		ResultSet rs = null;
		String sql = "SELECT YEAR(bbs_ptime),MONTH(bbs_ptime),bbs_sf,if(length(bbs_cs)>1,bbs_cs,bbs_sf) as cs,COUNT(bbs_bbsid) FROM autohome_syn."
				+ tablename + " GROUP BY bbs_sf,cs,YEAR(bbs_ptime),MONTH(bbs_ptime)";
		String ss = "";
		try {
			st = conn.createStatement();
			rs = st.executeQuery(sql);
			while (rs.next()) {
				int carid = Integer.parseInt(tablename.split("_")[2]);
				String carname = this.carname.get(carid);
				int year = rs.getInt(1);
				int month = rs.getInt(2);
				String province = rs.getString(3);
				String city = rs.getString(4);
				city = city.length() > 1 ? city : province;
				String rank = "";
				String area = "";
				ss = province + "\t" + city;
				if (city.contains("%")) {
					continue;
				}
				if (city.length() > 1) {
					rank = this.city.get(city).split("-")[0];
					area = this.city.get(city).split("-")[1];
				} else {
					if (!province.equals("其他")) {
						rank = this.city.get(province).split("-")[0];
						area = this.city.get(province).split("-")[1];
					}
				}
				int count = rs.getInt(5);
				Slrank s = new Slrank(carid, carname, year, month, province, city, rank, count, area);
				insert(s, "bbs");
			}
		} catch (Exception e) {
			System.out.println(tablename + "\t" + ss);
		} finally {
			try {
				rs.close();
				st.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	/**
	 * @Title: calculateKB
	 * @Description: TODO 计算声量
	 * @return void
	 * @author Alexis
	 */
	public void calculateKB() {
		Statement st = null;
		ResultSet rs = null;
		String sql = "SELECT YEAR(kb_ptime),MONTH(kb_ptime),kb_province,COUNT(kb_cid),kb_cid FROM autohome_syn.autohome_kb GROUP BY kb_province,YEAR(kb_ptime),MONTH(kb_ptime),kb_cid";
		try {
			st = conn.createStatement();
			rs = st.executeQuery(sql);
			while (rs.next()) {
				int year = rs.getInt(1);
				int month = rs.getInt(2);
				String province = rs.getString(3);
				int count = rs.getInt(4);
				int carid = rs.getInt(5);
				String carname = this.carname.get(carid);
				String rank = "";
				String area = "";
				if (province.length() > 1) {
					if (!province.equals("其他")) {
						rank = this.city.get(province).split("-")[0];
						area = this.city.get(province).split("-")[1];
					}
				}
				Slrank s = new Slrank(carid, carname, year, month, "", province, rank, count, area);
				insert(s, "kb");
			}
		} catch (Exception e) {
			System.out.println("KB出现异常，重新对地区信息。");
		}
	}

	public void insert(Slrank s, String type) {
		PreparedStatement ps = null;
		String sql = "INSERT INTO carvoice_biz.biz_slrank_" + type
				+ "_temp(slrank_cid,slrank_cname,slrank_year,slrank_month,slrank_province,slrank_city,slrank_level,slrank_area,slrank_count) VALUES(?,?,?,?,?,?,?,?,?)";
		try {
			ps = conn.prepareStatement(sql);
			ps.setInt(1, s.getCarid());
			ps.setString(2, s.getCarname());
			ps.setInt(3, s.getYear());
			ps.setInt(4, s.getMonth());
			ps.setString(5, s.getProvince());
			ps.setString(6, s.getCity());
			ps.setString(7, s.getRank());
			ps.setString(8, s.getArea());
			ps.setInt(9, s.getCount());
			ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				ps.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	public void run() {
		calculateBBS();
		System.out.println(tablename);
	}

	public Slrank1Dao(Connection conn, String tablename, Map<Integer, String> carname, Map<String, String> city) {
		super();
		this.conn = conn;
		this.tablename = tablename;
		this.carname = carname;
		this.city = city;
	}

	public void updateBizID() {
		String sql1 = "UPDATE biz_slrank_bbs SET slrank_bid=(SELECT brand_id FROM biz_brand WHERE brand_autohome=slrank_cid);";
		String sql2 = "UPDATE biz_slrank_kb SET slrank_bid=(SELECT brand_id FROM biz_brand WHERE brand_autohome=slrank_cid);";
		Statement st = null;
		try {
			st = conn.createStatement();
			st.executeUpdate(sql1);
			st.executeUpdate(sql2);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public void slrankCnt() {
		CallableStatement cs = null;
		try {
			cs = conn.prepareCall("{call SlrankCnt()}");
			cs.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public static void main(String[] args) throws Exception {
		Connection conn = ConnectionPool.getConnection("carvoice_biz", "localhost");
		List<String> tables = SlrankUtil.getBBSTable();
		TableUtil.createtable();
		Map<Integer, String> carname = SlrankUtil.getCarname();
		Map<String, String> city = SlrankUtil.getCityLevel();
		ExecutorService fix = Executors.newFixedThreadPool(15);
		for (String s : tables) {
			fix.execute(new Slrank1Dao(conn, s, carname, city));
		}
		fix.shutdown();
		while (true) {
			if (fix.isTerminated()) {
				System.out.println("BBSGG ");
				Slrank1Dao dao = new Slrank1Dao(conn, "", carname, city);
				dao.calculateKB();
				System.out.println("KBGG");
				TableUtil.droptable();
				dao.updateBizID();
				System.out.println("Update GG");
				dao.slrankCnt();
				System.out.println("Finish");
				break;
			}
			Thread.sleep(50000);
		}
	}
}
